using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Diagnostics;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace ePlanSysOman
{
  public class clsData2
  {
      
    public DataSet getData()
    {
      DataSet tempdataSet = new DataSet();//JMR -- DEV -- look at -- because getData = dataSet sets getData to return dataSet at the END of the function, I am moving the return statement down to the bottom and returning the tempdataSet --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something/26469050?noredirect=1#comment41576085_26469050
        
      Application.DoEvents();
      Module1.newModule.InitConnection();
      //  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ni
      // Create a SqlConnection to the  database.
      using (OleDbConnection connection = new OleDbConnection(Module1.newModule.Cn.ConnectionString)) {
        // Create a SqlDataAdapter for the Suppliers table.
        OleDbDataAdapter suppliersAdapter = new OleDbDataAdapter();

        // A table mapping names the DataTable.
        suppliersAdapter.TableMappings.Add("Table", "Operations");

        // Open the connection.
        connection.Open();

        // Create a SqlCommand to retrieve Suppliers data.
        OleDbCommand suppliersCommand = new OleDbCommand("SELECT DISTINCT REFDES,REPORTNOTE,REFDESID FROM TBLREFDES,TBLWIRES WHERE OPN = 2 AND REFDESID =WREFDESID and WIRESPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = WIRESPLID", connection);
        suppliersCommand.CommandType = CommandType.Text;

        // Set the SqlDataAdapter's SelectCommand.
        suppliersAdapter.SelectCommand = suppliersCommand;

        // Fill the DataSet.
        DataSet dataSet = new DataSet("Operations");
        suppliersAdapter.Fill(dataSet);

        // Create a second SqlDataAdapter and SqlCommand to get
        // the Products table, a child table of Suppliers. 
        OleDbDataAdapter productsAdapter = new OleDbDataAdapter();
        productsAdapter.TableMappings.Add("Table", "refdescomp");

        OleDbCommand productsCommand = new OleDbCommand("SELECT WIRENO,PIN,TERM,WIREACTID,WREFDESID FROM TBLWIRES,TBLREFDES WHERE (WIREACTID = 'ADD'  OR WIREactid ='REMOVE') AND REFDESID = REFDESID AND WREFDESID = REFDESID and WIRESPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = WIRESPLID", connection);
        productsAdapter.SelectCommand = productsCommand;

        // Fill the DataSet.
        productsAdapter.Fill(dataSet);

        OleDbDataAdapter partsAdapter = new OleDbDataAdapter();
        partsAdapter.TableMappings.Add("Table", "refdescompparts");

        OleDbCommand partsCommand = new OleDbCommand("SELECT  * FROM TBLREFDESCOMP,TBLREFDES WHERE  REFDESID = REFDESID AND REFDESCOMID = REFDESID AND REFCOMPPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = REFCOMPPLID", connection);
        partsAdapter.SelectCommand = partsCommand;

        // Fill the DataSet.
        partsAdapter.Fill(dataSet);

        //getData = dataSet;//JMR -- DEV -- look at -- changed it to the line below --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something
        tempdataSet = dataSet;

        //  productsAdapter.FillSchema(dataSet, SchemaType.Mapped, "Orders")


        //   dt = dataSet.Tables("OPERATIONS").Columns("REFDESID")
        //   dataSet.Tables("OPERATIONS").Columns.Remove(dt)
        dataSet.WriteXmlSchema(Application.StartupPath + "2.xml");



        // Close the connection.
        connection.Close();
        // Console.WriteLine("The SqlConnection is closed.")

        // Create a DataRelation to link the two tables
        // based on the SupplierID.
        //Dim parentColumn As DataColumn = _
        //   dataSet.Tables("Operations").Columns("Actid")
        //Dim childColumn As DataColumn = _
        //   dataSet.Tables("refdescomp").Columns("refactid")
        //Dim relation As DataRelation = New  _
        //   System.Data.DataRelation("Operationsrefdescomp", _
        //   parentColumn, childColumn)
        //dataSet.Relations.Add(relation)

        
      }
      return tempdataSet; //JMR -- DEV -- moved from above
    }

    public DataSet getDataBench()
    {
        DataSet tempdataSet = new DataSet();//JMR -- DEV -- look at -- because getData = dataSet sets getData to return dataSet at the END of the function, I am moving the return statement down to the bottom and returning the tempdataSet --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something/26469050?noredirect=1#comment41576085_26469050
        
      Application.DoEvents();
      Module1.newModule.InitConnection();
      //  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ni
      // Create a SqlConnection to the  database.
      using (OleDbConnection connection = new OleDbConnection(Module1.newModule.Cn.ConnectionString))
      {
        // Create a SqlDataAdapter for the Suppliers table.
        OleDbDataAdapter suppliersAdapter = new OleDbDataAdapter();

        // A table mapping names the DataTable.
        suppliersAdapter.TableMappings.Add("Table", "Operations");

        // Open the connection.
        connection.Open();

        // Create a SqlCommand to retrieve Suppliers data.
        OleDbCommand suppliersCommand = new OleDbCommand("SELECT DISTINCT REFDES,REPORTNOTE,REFDESID FROM TBLREFDES,TBLWIRES WHERE OPN = 5 AND REFDESID =WREFDESID and WIRESPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = WIRESPLID", connection);
        suppliersCommand.CommandType = CommandType.Text;

        // Set the SqlDataAdapter's SelectCommand.
        suppliersAdapter.SelectCommand = suppliersCommand;

        // Fill the DataSet.
        DataSet dataSet = new DataSet("Operations");
        suppliersAdapter.Fill(dataSet);

        // Create a second SqlDataAdapter and SqlCommand to get
        // the Products table, a child table of Suppliers. 
        OleDbDataAdapter productsAdapter = new OleDbDataAdapter();
        productsAdapter.TableMappings.Add("Table", "refdescomp");

        OleDbCommand productsCommand = new OleDbCommand("SELECT WIRENO,PIN,TERM,WIREACTID,WREFDESID FROM TBLWIRES,TBLREFDES WHERE  WIREactid = 'BENCHWORK' AND REFDESID = REFDESID AND WREFDESID = REFDESID and WIRESPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = WIRESPLID", connection);
        productsAdapter.SelectCommand = productsCommand;

        // Fill the DataSet.
        productsAdapter.Fill(dataSet);

        OleDbDataAdapter partsAdapter = new OleDbDataAdapter();
        partsAdapter.TableMappings.Add("Table", "refdescompparts");

        OleDbCommand partsCommand = new OleDbCommand("SELECT  * FROM TBLREFDESCOMP,TBLREFDES WHERE  REFDESID = REFDESID AND REFDESCOMID = REFDESID AND REFCOMPPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = REFCOMPPLID", connection);
        partsAdapter.SelectCommand = partsCommand;

        // Fill the DataSet.
        partsAdapter.Fill(dataSet);

        //getDataBench = dataSet;//JMR -- DEV -- look at -- changed it to the line below --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something
        tempdataSet = dataSet;

        //  productsAdapter.FillSchema(dataSet, SchemaType.Mapped, "Orders")


        dataSet.WriteXmlSchema(Application.StartupPath + "2.xml");



        // Close the connection.
        connection.Close();
        // Console.WriteLine("The SqlConnection is closed.")

        // Create a DataRelation to link the two tables
        // based on the SupplierID.
        //Dim parentColumn As DataColumn = _
        //   dataSet.Tables("Operations").Columns("Actid")
        //Dim childColumn As DataColumn = _
        //   dataSet.Tables("refdescomp").Columns("refactid")
        //Dim relation As DataRelation = New  _
        //   System.Data.DataRelation("Operationsrefdescomp", _
        //   parentColumn, childColumn)
        //dataSet.Relations.Add(relation)

        
      }
      return tempdataSet;//JMR -- DEV -- Moved from above
    }
    public DataSet getDataCutOff()
    {
        DataSet tempdataSet = new DataSet();//JMR -- DEV -- look at -- because getData = dataSet sets getData to return dataSet at the END of the function, I am moving the return statement down to the bottom and returning the tempdataSet --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something/26469050?noredirect=1#comment41576085_26469050
        
      Application.DoEvents();
      Module1.newModule.InitConnection();
      //  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ni
      // Create a SqlConnection to the  database.
      using (OleDbConnection connection = new OleDbConnection(Module1.newModule.Cn.ConnectionString))
      {
        // Create a SqlDataAdapter for the Suppliers table.
        OleDbDataAdapter suppliersAdapter = new OleDbDataAdapter();

        // A table mapping names the DataTable.
        suppliersAdapter.TableMappings.Add("Table", "Operations");

        // Open the connection.
        connection.Open();

        // Create a SqlCommand to retrieve Suppliers data.
        OleDbCommand suppliersCommand = new OleDbCommand("SELECT DISTINCT REFDES,REPORTNOTE,REFDESID FROM TBLREFDES,TBLWIRES WHERE OPN = 1 AND REFDESID =WREFDESID and WIRESPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = WIRESPLID", connection);
        suppliersCommand.CommandType = CommandType.Text;

        // Set the SqlDataAdapter's SelectCommand.
        suppliersAdapter.SelectCommand = suppliersCommand;

        // Fill the DataSet.
        DataSet dataSet = new DataSet("Operations");
        suppliersAdapter.Fill(dataSet);

        // Create a second SqlDataAdapter and SqlCommand to get
        // the Products table, a child table of Suppliers. 
        OleDbDataAdapter productsAdapter = new OleDbDataAdapter();
        productsAdapter.TableMappings.Add("Table", "refdescomp");

        OleDbCommand productsCommand = new OleDbCommand("SELECT WIRENO,PIN,TERM,WIREACTID,WREFDESID FROM TBLWIRES,TBLREFDES WHERE   WIREACTID = 'CUTOFF' AND REFDESID = REFDESID AND WREFDESID = REFDESID and WIRESPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = WIRESPLID", connection);
        productsAdapter.SelectCommand = productsCommand;

        // Fill the DataSet.
        productsAdapter.Fill(dataSet);

        OleDbDataAdapter partsAdapter = new OleDbDataAdapter();
        partsAdapter.TableMappings.Add("Table", "refdescompparts");

        OleDbCommand partsCommand = new OleDbCommand("SELECT  * FROM TBLREFDESCOMP,TBLREFDES WHERE REFDESID = REFDESID AND REFDESCOMID = REFDESID AND REFCOMPPLID = " + Module1.newModule.PlanNumber + " AND RefPlanId = REFCOMPPLID", connection);
        partsAdapter.SelectCommand = partsCommand;

        // Fill the DataSet.
        partsAdapter.Fill(dataSet);

        //getDataCutOff = dataSet;//JMR -- DEV -- look at -- changed it to the line below --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something
        tempdataSet = dataSet;
        

        //  productsAdapter.FillSchema(dataSet, SchemaType.Mapped, "Orders")


        //   dt = dataSet.Tables("OPERATIONS").Columns("REFDESID")
        //   dataSet.Tables("OPERATIONS").Columns.Remove(dt)
        dataSet.WriteXmlSchema(Application.StartupPath + "2.xml");



        // Close the connection.
        connection.Close();
        // Console.WriteLine("The SqlConnection is closed.")

        // Create a DataRelation to link the two tables
        // based on the SupplierID.
        //Dim parentColumn As DataColumn = _
        //   dataSet.Tables("Operations").Columns("Actid")
        //Dim childColumn As DataColumn = _
        //   dataSet.Tables("refdescomp").Columns("refactid")
        //Dim relation As DataRelation = New  _
        //   System.Data.DataRelation("Operationsrefdescomp", _
        //   parentColumn, childColumn)
        //dataSet.Relations.Add(relation)

        
      }
      return tempdataSet;
    }
      
  }




}

